<?php
#!/bin/bash

/**
 * User: hzbskak
 * Date: 2019/12/31 - 9:16
 */

require_once "DB.php";
require_once "PHPExcel/PHPExcel.php";
$db = new DB();

// 文件名和文件类型
$fileName = "student";
$fileType = "xlsx";
ini_set('memory_limit','5600M'); //设置程序运行的内存
ini_set('max_execution_time',0); //设置程序的执行时间,0为无上限
ini_set('display_errors', 1); // 开启报错
error_reporting( E_ALL ); //设置报错等级
// 百万数据
$sql = " select * from ht_price"; // 一开始测试可以用limit控制条数
$data = $db->getAll( $sql);
// 最大列数
$sql2 = "select count(1) from information_schema.COLUMNS where table_name = 'ht_price'";
$column_max = $db->getRow( $sql2)['count(1)'];
// 表的字段key value
$sql3 = "select column_name as k,column_comment as v from information_schema.columns where table_name='ht_price'";
$kv = $db->getAll( $sql3);
// 少量数据
if( count( $data) < 10000)
{
    $obj = new \PHPExcel();

    // 以下内容是excel文件的信息描述信息
    $obj->getProperties()->setCreator('创建者11'); //设置创建者
    $obj->getProperties()->setLastModifiedBy('修改者11'); //设置修改者
    $obj->getProperties()->setTitle('标题11'); //设置标题
    $obj->getProperties()->setSubject('主题11'); //设置主题
    $obj->getProperties()->setDescription('描述11'); //设置描述
    $obj->getProperties()->setKeywords('关键词11');//设置关键词
    $obj->getProperties()->setCategory('类型11');//设置类型

    // 设置当前sheet
    $obj->setActiveSheetIndex(0);

    // 设置当前sheet的名称
    $obj->getActiveSheet()->setTitle('sheet11');


    // 循环列 输出 a-z-zz
    $list = [];
    for ($i = 0; $i <= $column_max-1; $i++) {

        $y = ($i / 26);
//    echo $i,"=>";
        if ($y >= 1) {
            $y = intval($y);
            $a = chr($y+64);
            $b = chr($i-$y*26 + 65);
            $list [] = $a . $b;
        } else {
            $c = chr($i+65);
            $list [] = $c;
        }
//    echo "\r\n";
    }

    // 填充第一行数据
    $sheet=  $obj->getActiveSheet();
    foreach ( $kv as $k => $v)
    {
        $sheet->setCellValue( $list[$k] . '1', $v['v']);
    }

    // 填充第n(n>=2, n∈N*)行数据
    $length = count($data);
    foreach ( $data as $k => $v)
    {
        foreach ( $kv as $kk => $vv)
        {
            // 需要转换成文本的值手动加空格
            if( $vv['k'] == 'market_code')
            {
                $sheet->setCellValue( $list[$kk] . ($k + 2), $v[$vv['k']] . ' ',
                    \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
            }
            elseif( $vv['k'] == 'date')
            {
                $v[$vv['k']] = date( 'Y-m-d H:i:s', $v[$vv['k']]);
                $sheet->setCellValue( $list[$kk] . ($k + 2), $v[$vv['k']],
                    \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
            }
            else
            {
                $sheet->setCellValue( $list[$kk] . ($k + 2), $v[$vv['k']],
                    \PHPExcel_Cell_DataType::TYPE_STRING);//将其设置为文本格式
            }
        }
    }
    // 设置加粗和左对齐
    foreach ($list as $col) {
        // 设置第一行加粗
        $obj->getActiveSheet()->getStyle($col . '1')->getFont()->setBold(true);
        // 设置第1-n行，左对齐
        for ($i = 1; $i <= $length + 1; $i++) {
            $obj->getActiveSheet()->getStyle($col . $i)->getAlignment()->setHorizontal(\PHPExcel_Style_Alignment::HORIZONTAL_LEFT);
        }
    }

    // 设置列宽
    $obj->getActiveSheet()->getColumnDimension('D')->setWidth(20);

    // 导出
    ob_clean();
    if ($fileType == 'xls') {
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xls');
        header('Cache-Control: max-age=1');
        $objWriter = new \PHPExcel_Writer_Excel5($obj);
        $objWriter->save('php://output');
        exit;
    } elseif ($fileType == 'xlsx') {
        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="' . $fileName . '.xlsx');
        header('Cache-Control: max-age=1');
        $objWriter = \PHPExcel_IOFactory::createWriter($obj, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }
}
else
{
    // 大量数据 导出到csv
    ob_end_clean();  //清除内存
    ob_start();
    header("Content-Type: text/csv");
    header("Content-Disposition:filename=".$fileName.'.csv');
    // value列名
    $ks = array_column( $kv, 'v');

    $fp=fopen('php://output','w');
    fwrite( $fp,  chr(0xEF).chr(0xBB).chr(0xBF));
    fputcsv($fp, $ks);
    $index = 0;
    foreach ($data as &$item) {
        // 防止出现科学计数法
        $item['market_code'] = $item['market_code'] . "\t";
        // unix时间戳转日期
        $item['date'] = date( 'Y-m-d H:i:s', $item['date']);
        if($index==1000){ //每次写入1000条数据清除内存
            $index=0;
            ob_flush();//清除内存
            flush();
        }
        $index++;
        fputcsv($fp,$item);
    }

    ob_flush();
    flush();
    ob_end_clean();
    exit();
}


function echoJson($arr)
{
    echo json_encode($arr);
    exit;
}
